Let's talk about VLOOKUP. This function is often the turning point for a lot of Excel users. I know it was for me, and I've talked with a lot of people. VLOOKUP has multiple components to it, and it's not so straightforward. Like if you have sum, you can go to a sale, click equals sum, open parentheses, highlight your range, boom, then you've got your sum. But VLOOKUP requires you to slow down and think. And once a person has that and they're comfortable with VLOOKUP, they are like wide open. They are free to go just about anywhere they want in Excel. So I'm going to help you with that turning point, if you aren't already there. But I also have to say XLOOKUP came out a few years ago, and it was designed to replace VLOOKUP because VLOOKUP has some weaknesses and I will point those out. So let's get into the data, enough of me talking. All right, we have this data. We got CC-5M0749, the transaction was for $84 and 14 cents. We want to get the category and the level of the transaction. Okay, real life situation, you've got a report that came in with the ID and the amount but now we've been tasked to match up the category and the level. Oh, okay. Over here we have the ID, the name and the specialization, the specialization is the category, that's what we want to retrieve. And then for each amount we want to assign a level to it, okay? The specialization data is not in a table and that's deliberate cause I need to show you some things and this data, yeah let's go ahead and put this into a table. The cursor is within A data set. I'm going to go format as table. Let's grab black one, table does have headers. Yes. Okay, get rid of the filter buttons. Alright, equals VLOOKUP, double click, look up what? Look up that first ID comma, the table array. Where do we want to look for that ID? We want to go over here, to this data set. And for VLOOKUP, we have to copy the data that we want to look up and the data that we want to retrieve, it's all got to be here, even though we don't want the name, we have to copy that. And then I'm going to hit F4 because when the formula goes down the column on the transactions page I do not want this moving on me. Alright, comma column index number, which column do I want it to bring back? I want one, two, three, specialization, that's the third column. Three, comma range lookup. Do I want an approximate match or an exact match? I want an exact match and I'm going to type zero and then close parentheses, enter, alright CC-H2VUKI, It's saying electronics, let's check that. Let's go back. Yep, that is Jean-Pierre and electronics. Good, now let's go back to transactions. Okay, we've got food, safety, electronics, arts, and crafts. All right, so we have some N/A's. And let's say that we know that N/A's means that this transaction was processed by somebody who no longer works here. So we're going to have to set these aside and figure out the category separate. Okay, but what we can do is go up to the formula and wrap this in if N/A because it was not found open parentheses, okay, we have the value and what do we want to put in replace of just the hashtag N/A error. Say ah, no longer employed. And it has to be in double quotes and close parentheses for the if N/A and enter. And notice, because this is in a table, I made that change in one cell and the formula has populated throughout the entire column, double click there. Okay, And also, let's ask, why would we want to do something like this? Well, we might want to use a pivot table to compare the food safety values against electronics and event planning et cetera. We might only be concerned about automotive data and we need to get everything categorized so that we can then filter everything else out. Lots of reasons why we would want to match this up. Okay, now we need to level, now we're going to do something different with the VLOOKUP. Go over the level, equals VLOOKUP, double click it once more, look up what? We want to look up the dollar amount, comma the table array. Go back to reps and levels, highlight both of the columns, comma column index number, we want the second column, comma, and we want true for an approximate match. What that will do is from zero up to a hundred, The assignment is going to be L1. So we don't need say an $83 and 4 cents, specifically in this table, when we do an approximate match. So I'm going to go and put one for true because I Want the approximate match, close parentheses and enter. There we are, we have an XL here, for the $736.66 Let's look at what's happening there. Anything $700 and above will get XL. And one thing about VLOOKUP, in order for the approximate match to work, this column does have to be sorted ascending. So that's VLOOKUP for you. Let's go back to the transactions, and a next step could be to wrap the level in an if N/A and pick what we want that to show instead of the error message, or we can leave it as an error message. But right now we've got what we needed, and VLOOKUP made it pretty simple. One requirement of VLOOKUP, the column that has your lookup values has to be the left most. And what you want to retrieve has to be to the right of that. There could be 1, 2, 50. How many ever columns to the right. But VLOOKUP cannot be look leftward.